package com.tools.sqllite
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.utils.ByteArray;

	/**
	 * 本地数据库管理
	 * @author Boder
	 * @Time 2011-9-26 11:45:18
	 */
	public class SqlManager
	{
		private var con : SQLConnection;
		private var createStmt : SQLStatement;

		public function SqlManager()
		{

			var file : File = File.documentsDirectory.resolvePath( "yo2r" ); // File.desktopDirectory.resolvePath("yo2r.db"); //

			if( !file.exists )
			{
				file.createDirectory();
			}
			file = file.resolvePath( "yo2r.db" );
			con = new SQLConnection();
			//在 openAsync() 方法调用操作成功完成时调度
			con.addEventListener( SQLEvent.OPEN, openHandler );
			//SQLConnection 对象的异步操作导致错误时调度
			con.addEventListener( SQLErrorEvent.ERROR, errorHandler );
			con.open( file );
		}

		/**
		 * 返回所有保存过的登陆信息
		 * @return
		 *
		 */
		public function getLoginInfos() : Array
		{
			var sql : String             = "SELECT * FROM loginInfo ORDER BY isDefault DESC";
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			//statement.itemClass  =  LoginVo;
			statement.execute();
			//statement.addEventListener(SQLEvent.RESULT, doLoadLoginInfoResult);
			// execute the statement 
			var result : SQLResult       = statement.getResult();
			return result.data;
		}

		/**
		 * 取出历史聊天记录
		 * @param id 用户或群id
		 * @param isGroup 是否是群消息
		 * @return 返回所有历史聊天记录
		 *
		 */
		public function getMessages( id : Number, isGroup : Boolean = false ) : Array
		{
			if( isGroup )
			{
				// define the SQL text 
				var sql : String = "select * from groupHisMsg where groupId = ? ORDER BY id DESC";
			}
			else
			{
				sql = "select * from userHisMsg where userId = ? ORDER BY id DESC";
			}

			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			statement.parameters[ 0 ] = id;
			statement.execute();
			var result : SQLResult       = statement.getResult();
			var tmpArr : Array           = result.data;
			var rtnArr : Array           = [];

			var i_len : int              = tmpArr.length;

			for( var i : int = 0; i<i_len; i++ )
			{
				var bytes : ByteArray = rtnArr[ i ].msgObj as ByteArray;
				bytes.uncompress();
				var arr : Array       = bytes.readObject() as Array;
				rtnArr.concat( arr );
			}
			return rtnArr;
		}

		/**
		 * 获得已经打开的im任务
		 * @return
		 *
		 */
		public function getOpenedApp() : int
		{
			var sql : String             = "SELECT *  FROM verIdForm LIMIT 1";
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			statement.execute();
			// execute the statement 
			var result : SQLResult       = statement.getResult();

			if( result.data )
			{
				var rtn : int = result.data[ 0 ].openedApp;
			}
			else
			{
				rtn = 1;
			}
			return rtn;
		}

		/**
		 * 返回应用id
		 * @return
		 *
		 */
		public function getVerId() : int
		{
			var sql : String             = "SELECT *  FROM verIdForm LIMIT 1";
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			statement.execute();
			// execute the statement 
			var result : SQLResult       = statement.getResult();

			if( result.data&&result.data.length>0 )
			{
				var rtnId : int  = result.data[ 0 ].verId;
				var opened : int = result.data[ 0 ].openedApp;

				sql = "UPDATE verIdForm set verId=?,openedApp=?";
				statement.parameters[ 0 ] = rtnId+1;
				statement.parameters[ 1 ] = opened+1;

			}
			else
			{
				rtnId = 0;
				sql = "INSERT INTO verIdForm ( verId,openedApp) VALUES ( 1,1 )";
			}

			statement.text = sql;
			statement.execute();
			return rtnId;
		}

		/**
		 * 插入历史聊天消息
		 * @param id 用户或群id
		 * @param msgBytes 消息字节
		 * @param isGroup 是否是群消息
		 *
		 */
		public function insetMessage( id : Number, msgBytes : ByteArray, isGroup : Boolean = false ) : void
		{

			if( isGroup )
			{
				// define the SQL text 
				var sql : String = "INSERT INTO groupHisMsg ( groupId, msgObj) VALUES ( ?, ? )";
			}
			else
			{
				sql = "INSERT INTO userHisMsg ( userId, msgObj) VALUES ( ?, ? )";
			}
			trace( "压缩前字节数:"+msgBytes.length );
			msgBytes.compress();
			trace( "压缩后字节数:"+msgBytes.length );
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			statement.parameters[ 0 ] = id;
			statement.parameters[ 1 ] = msgBytes;
			statement.execute();
		}

		/**
		 * 加入一条登陆信息
		 * @param username 用户名
		 * @param password 密码
		 * @param isRember 是否记住密码
		 * @param autoEnter 是否自动登陆
		 * @return
		 *
		 */
		public function saveLoginInfo( username : String, password : String = "", enterType : int = 0, headImg : String = "" ) : void
		{

			var sql : String             = "SELECT username  FROM loginInfo  WHERE username = :username ";
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			// set parameter values 
			statement.parameters[ ":username" ] = username;
			statement.execute();
			// execute the statement 
			var result : SQLResult       = statement.getResult();

			statement.clearParameters();

			sql = "UPDATE loginInfo set isDefault=?";
			statement.parameters[ 0 ] = false;
			statement.text = sql;
			statement.execute();
			statement.clearParameters();

			if( result.data&&result.data.length>0 )
			{ //如果已经存在
				sql = "UPDATE loginInfo set username=?, password=?, enterType=?, headImg=?, isDefault=? where username = ?";
				statement.parameters[ 0 ] = username;
				statement.parameters[ 1 ] = password;
				statement.parameters[ 2 ] = enterType;
				statement.parameters[ 3 ] = headImg;
				statement.parameters[ 4 ] = true;
				statement.parameters[ 5 ] = username;
				statement.text = sql;
			}
			else
			{
				// define the SQL text 
				sql = "INSERT INTO loginInfo ( username, password, enterType, headImg, isDefault ) VALUES ( ?, ?, ?, ?, ? )";
				statement.parameters[ 0 ] = username;
				statement.parameters[ 1 ] = password;
				statement.parameters[ 2 ] = enterType;
				statement.parameters[ 3 ] = headImg;
				statement.parameters[ 4 ] = true;
				statement.text = sql;

			}
			// execute the statement 
			statement.execute();
		}

		/**
		 * 设置已打开的任务进程数量
		 * @param opened 当前打开的任务进程数量,之前应该是调用getOpenedApp()取出来并计算之后需要设置的值
		 *
		 */
		public function setOpenedApp( opened : int ) : void
		{
			var sql : String             = "UPDATE verIdForm set openedApp=?";
			var statement : SQLStatement = new SQLStatement();
			statement.sqlConnection = con;
			statement.text = sql;
			statement.parameters[ 0 ] = opened;
			statement.execute();
		}

		/**
		 * 创建失败处理
		 * @param event
		 *
		 */
		protected function createError( event : SQLErrorEvent ) : void
		{
			//var veridStmt  :  SQLStatement  =  event.target as SQLStatement;
			//trace(veridStmt.text);
		}

		/**
		 * 创建成功处理
		 * @param event
		 *
		 */
		protected function createResult( event : SQLEvent ) : void
		{
			createStmt.removeEventListener( SQLEvent.RESULT, createResult );
			createStmt.removeEventListener( SQLErrorEvent.ERROR, createError );
		}

		/**
		 * 加载登陆信息成功
		 * @param event
		 *
		 */
		protected function doLoadLoginInfoResult( event : SQLEvent ) : void
		{
			//var result  :  SQLResult  =  (event.target as SQLStatement).getResult();
		}

		/**
		 * 异步操作导致错误时调度
		 * @param event
		 *
		 */
		protected function errorHandler( event : SQLErrorEvent ) : void
		{

		}

		/**
		 * 调用操作成功完成时调度
		 * @param event
		 *
		 */
		protected function openHandler( event : SQLEvent ) : void
		{
			//创建登陆信息表
			createStmt = new SQLStatement();
			createStmt.sqlConnection = con;
			/**
			 * username:用户名
			 * password
			 * headImg:头像
			 * enterType:登陆类型,0默认,1记住密码,2自动登陆
			 * isDefault:是否是默认选中的登陆对象
			 * */
			var sql : String             = "CREATE TABLE IF NOT EXISTS loginInfo ( id INTEGER PRIMARY KEY AUTOINCREMENT, username NOT NULL,  password NULL,  headImg NULL,  enterType NULL, isDefault NULL)";
			createStmt.text = sql;
			createStmt.addEventListener( SQLEvent.RESULT, createResult );
			createStmt.addEventListener( SQLErrorEvent.ERROR, createError );
			createStmt.execute();
			con.removeEventListener( SQLEvent.OPEN, openHandler );
			con.removeEventListener( SQLErrorEvent.ERROR, errorHandler );
			//创建应用id变量保存
			var veridStmt : SQLStatement = new SQLStatement();
			veridStmt.sqlConnection = con;
			sql = "CREATE TABLE IF NOT EXISTS verIdForm ( id INTEGER PRIMARY KEY AUTOINCREMENT, verId INTEGER NOT NULL,openedApp INTEGER NULL)";
			veridStmt.text = sql;
			veridStmt.addEventListener( SQLEvent.RESULT, veridStmtResult );
			veridStmt.execute();
			//创建用户历史聊天记录表 用户id,消息内容,时间
			veridStmt = new SQLStatement();
			veridStmt.sqlConnection = con;
			sql = "CREATE TABLE IF NOT EXISTS userHisMsg ( id INTEGER PRIMARY KEY AUTOINCREMENT, userId NOT NULL,msgObj NOT NULL)";
			veridStmt.text = sql;
			veridStmt.addEventListener( SQLEvent.RESULT, veridStmtResult );
			veridStmt.execute();
			//创建群历史聊天记录表 群id,消息内容,时间
			veridStmt = new SQLStatement();
			veridStmt.sqlConnection = con;
			sql = "CREATE TABLE IF NOT EXISTS groupHisMsg ( id INTEGER PRIMARY KEY AUTOINCREMENT, groupId NOT NULL,msgObj NOT NULL)";
			veridStmt.text = sql;
			veridStmt.addEventListener( SQLEvent.RESULT, veridStmtResult );
			veridStmt.execute();
		}

		/**
		 * 应用id表创建成功
		 * @param event
		 *
		 */
		protected function veridStmtResult( event : SQLEvent ) : void
		{
			var veridStmt : SQLStatement = event.target as SQLStatement;
			veridStmt.removeEventListener( SQLEvent.RESULT, veridStmtResult );
			//trace(veridStmt.text);
		}
	}
}
